package com.qdu.dao;

import com.qdu.entity.Users;
import com.qdu.service.impl.LogServiceImpl;
import com.qdu.util.DatabaseUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class UsersDao {
    
    //通过id获取用户
    public Users UserLogin(String Uid){  
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Users user=null;
        try {
            con=DatabaseUtil.getConnection();            
            ps=con.prepareStatement("select * from Users where Uid=?");
            ps.setString(1, Uid);
            rs = ps.executeQuery();
            if(rs.next()) {
                user=new Users(
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getString(9),
                        rs.getString(10),
                        rs.getInt(11),
                        rs.getInt(12),
                        rs.getString(13)
                        );
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally{
            DatabaseUtil.close(rs, ps, con);
        }
        return user;
    }
    
    //通过用户名查询用户
    public Users getOneUserByName(String name){
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Users user=null;
        try {
            con=DatabaseUtil.getConnection();            
            ps=con.prepareStatement("select * from Users where Uname=?");
            ps.setString(1, name);
            rs = ps.executeQuery();
            if(rs.next()) {
                user=new Users(
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getString(9),
                        rs.getString(10),
                        rs.getInt(11),
                        rs.getInt(12),
                        rs.getString(13)
                        );
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally{
            DatabaseUtil.close(rs, ps, con);
        }
        return user;
    }
    
    //查询所有用户
    public List<Users> getAllUsers(){
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Users> list=new ArrayList<>();
        try {
            con=DatabaseUtil.getConnection();            
            ps=con.prepareStatement("select * from Users");
            rs = ps.executeQuery();
            while(rs.next()) {
                list.add(new Users(
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getString(9),
                        rs.getString(10),
                        rs.getInt(11),
                        rs.getInt(12),
                        rs.getString(13)
                        )
                );
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally{
            DatabaseUtil.close(rs, ps, con);
        }
        return list;
    }
    
    //插入新用户
    public int insertNewUser(Users user)
    {
        Connection con=null;
        PreparedStatement ps=null;
        int rows=0;
        try{
            con=DatabaseUtil.getConnection();
            ps=con.prepareStatement("insert into Users values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
            ps.setString(1,"1");
            ps.setString(2,user.getUid());
            ps.setString(3,user.getUname());
            ps.setString(4,user.getUgender());
            ps.setString(5,user.getUpwd());
            ps.setString(6,user.getUbrithday());
            ps.setString(7,user.getUIDnumber());
            ps.setString(8,user.getUemail());
            ps.setString(9,user.getUtelnumber());
            ps.setString(10,user.getUcity());
            ps.setString(11,user.getUcareer());
            ps.setInt(12,0);
            ps.setInt(13,0);
            rows=ps.executeUpdate();
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            DatabaseUtil.close(null, ps, con);
        }
        return rows;
        
    }
    
    //根据id删除用户
    public int deleteOneUser(String id){
        //用户对应的活动和日志表也得删除
        LogServiceImpl logserviceimpl=new LogServiceImpl();
        Connection con=null;
        PreparedStatement ps=null;
        int rows=0;
        try{
            con=DatabaseUtil.getConnection();
            ps=con.prepareStatement("delete from SignUp where Uid=?;delete from ActiComment where Aid=(select Aid from Activity where Auserid=?);delete from Activity where Auserid=?;delete from LogComment where Lid=(select Lid from ActLog where Uid=?) or Uid=?;delete from ActLog where Uid=?;delete from Users where Uid=?");
            ps.setString(1, id);
            ps.setString(2, id);
            ps.setString(3, id);
            ps.setString(4, id);
            ps.setString(5, id);
            ps.setString(6, id);
            ps.setString(7, id);
            rows=ps.executeUpdate();
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            DatabaseUtil.close(null, ps, con);
        }
        return rows;
    }
    
    //修改用户审核状态
    public int modifyUserStatus(String id){
        Connection con=null;
        PreparedStatement ps=null;
        int rows=0;
        try{
            con=DatabaseUtil.getConnection();
            ps=con.prepareStatement("update Users set Ustatus=1,Ulevel=1 where Uid=?");
            ps.setString(1, id);
            rows=ps.executeUpdate();
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            DatabaseUtil.close(null, ps, con);
        }
        return rows;
    }
    
    //修改用户等级
    public int  modifyUserLevel(String id,int level){
        Connection con=null;
        PreparedStatement ps=null;
        int rows=0;
        try{
            con=DatabaseUtil.getConnection();
            ps=con.prepareStatement("update Users set Ulevel=? where Uid=?");
            ps.setInt(1,level);
            ps.setString(2,id);
            rows=ps.executeUpdate();
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            DatabaseUtil.close(null, ps, con);
        }
        return rows;
        
    }
    
    }
    
